Data check

Introduction

We present here some preliminary experiments in order to give an overview of the possibilities of analysis of world region based on the Food Balance Sheets (FBS) published by FAO. We import the data, realize some harmonization and check if our results are the same than the most recent report published by FAO FAO (2023).

Data collection

We use in a first step the most recent data produced by FAO, covering the period 2010 to present. We will further used another database covering the period 1963-2013 but not immediately as they are some difficulties for the harmonization of the two databases.

Geometry

We have prepared different geometry adapted to the 188 countries or territories available in the database.

Population

The FAO has collected variables from other sources (UN, Worldbank,…) in order to get estimates of the population of territorial units for which the FBS has been collected. We eliminate the unit called China (F351) because it is the sum of four other units present in the database China Mainland (CHN), Hong Kong (HKG), Macao (MAC) and … Taïwan (TWN). This duplication of data is clearly not practical from statistical point of view. But we can easily imagine why the republic of China has obliged the FAO to proceed this way …

Joining with `by = join_by(iso3)`
Population 2010-2022 - Long format
iso3 year pop name region continent
AFG 2010 28189.67 Afghanistan Southern Asia Asia
AFG 2011 29249.16 Afghanistan Southern Asia Asia
AFG 2012 30466.48 Afghanistan Southern Asia Asia
AFG 2013 31541.21 Afghanistan Southern Asia Asia
AFG 2014 32716.21 Afghanistan Southern Asia Asia
AFG 2015 33753.50 Afghanistan Southern Asia Asia

The dataset is in long format but can easily be transformed in wide format :

Population 2010-2022 - wide format
iso3 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022
AFG 28189.67 29249.16 30466.48 31541.21 32716.21 33753.50 34636.21 35643.42 36686.78 37769.50 38972.23 40099.46 41128.77
AGO 23364.19 24259.11 25188.29 26147.00 27128.34 28127.72 29154.75 30208.63 31273.53 32353.59 33428.49 34503.77 35588.99
ALB 2913.40 2900.65 2892.19 2887.01 2884.10 2882.48 2881.06 2879.36 2877.01 2873.88 2866.85 2854.71 2842.32
ARE 8481.77 8575.20 8664.97 8751.85 8835.95 8916.90 8994.26 9068.30 9140.17 9211.66 9287.29 9365.15 9441.13
ARG 41100.12 41520.74 41952.36 42388.27 42824.05 43257.07 43668.24 44054.62 44413.60 44745.52 45036.03 45276.78 45510.32
ARM 2946.29 2928.98 2914.42 2901.39 2889.93 2878.59 2865.84 2851.92 2836.56 2820.60 2805.61 2790.97 2780.47

Some missing values can be observed but only for a limited number of territorial units :

`summarise()` has grouped output by 'iso3'. You can override using the
`.groups` argument.
iso3 name available max pct
BHR Bahrain 4 13 30.8
BTN Bhutan 4 13 30.8
FSM Micronesia (Federated States of) 4 13 30.8
MHL Marshall Islands 4 13 30.8
NRU Nauru 4 13 30.8
QAT Qatar 4 13 30.8
SSD South Sudan 4 13 30.8
TON Tonga 4 13 30.8
PRK Democratic People’s Republic of Korea 9 13 69.2
SDN Sudan 11 13 84.6

We elaborate a map of the average share of world population over the period :

Joining with `by = join_by(iso3)`

Food Balance Sheet (Kcal)

We select the Food Balance Sheet (FBS) measured in Kcal/capita/day which is one of the four possible option of measure. Initialy the table has a lot of columns :

Structure of data

FBS 2010-2022 - Raw data
Domain Code Domain Area Code (ISO3) Area Element Code Element Item Code (FBS) Item Year Code Year Unit Value Flag Flag Description Note
FBS Food Balances (2010-) AFG Afghanistan 664 Food supply (kcal/capita/day) S2511 Wheat and products 2010 2010 kcal/cap/d 1469.35 E Estimated value NA
FBS Food Balances (2010-) AFG Afghanistan 664 Food supply (kcal/capita/day) S2511 Wheat and products 2011 2011 kcal/cap/d 1407.04 E Estimated value NA
FBS Food Balances (2010-) AFG Afghanistan 664 Food supply (kcal/capita/day) S2511 Wheat and products 2012 2012 kcal/cap/d 1358.96 E Estimated value NA
FBS Food Balances (2010-) AFG Afghanistan 664 Food supply (kcal/capita/day) S2511 Wheat and products 2013 2013 kcal/cap/d 1390.57 E Estimated value NA
FBS Food Balances (2010-) AFG Afghanistan 664 Food supply (kcal/capita/day) S2511 Wheat and products 2014 2014 kcal/cap/d 1362.96 E Estimated value NA
FBS Food Balances (2010-) AFG Afghanistan 664 Food supply (kcal/capita/day) S2511 Wheat and products 2015 2015 kcal/cap/d 1371.68 E Estimated value NA
FBS Food Balances (2010-) AFG Afghanistan 664 Food supply (kcal/capita/day) S2511 Wheat and products 2016 2016 kcal/cap/d 1402.60 E Estimated value NA
FBS Food Balances (2010-) AFG Afghanistan 664 Food supply (kcal/capita/day) S2511 Wheat and products 2017 2017 kcal/cap/d 1405.12 E Estimated value NA
FBS Food Balances (2010-) AFG Afghanistan 664 Food supply (kcal/capita/day) S2511 Wheat and products 2018 2018 kcal/cap/d 1365.34 E Estimated value NA
FBS Food Balances (2010-) AFG Afghanistan 664 Food supply (kcal/capita/day) S2511 Wheat and products 2019 2019 kcal/cap/d 1346.93 E Estimated value NA

We decide to simplify a bit the structure and to reduce the number of columns like this :

FBS 2010-2022 - Simplified
year iso3 item item_name K
2010 AFG S2511 Wheat and products 1469.35
2011 AFG S2511 Wheat and products 1407.04
2012 AFG S2511 Wheat and products 1358.96
2013 AFG S2511 Wheat and products 1390.57
2014 AFG S2511 Wheat and products 1362.96
2015 AFG S2511 Wheat and products 1371.68
2016 AFG S2511 Wheat and products 1402.60
2017 AFG S2511 Wheat and products 1405.12
2018 AFG S2511 Wheat and products 1365.34
2019 AFG S2511 Wheat and products 1346.93

Hierarchisation of food items

In the initial format delivered by FAO, the classification of items is a mixture of aggregates at different hierarchical level. Here we have decided to collect only the lowest level and to add a hierarchy of aggregation levels based on the table below :

Hierarchy of food items
i0 name_i0 i1 name_i1 i2 name_i2 i name_i
2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) 2511 WHEAT & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) 2513 BARLEY & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) 2514 MAIZE & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) 2515 RYE & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) 2516 OATS & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) 2517 MILLET & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) 2518 SORGHUM & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) 2520 CEREALS, OTHER & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2907 STARCHY ROOTS 2531 POTATOES & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2907 STARCHY ROOTS 2532 CASSAVA & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2907 STARCHY ROOTS 2533 SWEET POTATOES
2901 TOTAL 2903 VEGETALE PRODUCTS 2907 STARCHY ROOTS 2534 ROOTS, OTHER & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2907 STARCHY ROOTS 2535 YAMS
2901 TOTAL 2903 VEGETALE PRODUCTS 2908 SUGAR CROPS 2536 SUGAR CANE
2901 TOTAL 2903 VEGETALE PRODUCTS 2908 SUGAR CROPS 2537 SUGAR BEET
2901 TOTAL 2903 VEGETALE PRODUCTS 2909 SWEETENERS 2541 SUGAR NON-CENTRIFUGAL
2901 TOTAL 2903 VEGETALE PRODUCTS 2909 SWEETENERS 2542 SUGAR (RAW EQUIVALENT) & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2909 SWEETENERS 2543 SWEETENERS, NES & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2911 PULSES 2546 BEANS DRY
2901 TOTAL 2903 VEGETALE PRODUCTS 2911 PULSES 2547 PEAS DRY
2901 TOTAL 2903 VEGETALE PRODUCTS 2911 PULSES 2549 PULSES, OTHER & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2912 TREENUTS 2551 NUTS & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2913 OILCROPS 2552 GROUNDNUTS & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2913 OILCROPS 2555 SOYBEANS & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2913 OILCROPS 2557 SUNFLOWERSEED
2901 TOTAL 2903 VEGETALE PRODUCTS 2913 OILCROPS 2558 RAPE AND MUSTARDSEED & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2913 OILCROPS 2559 COTTONSEED
2901 TOTAL 2903 VEGETALE PRODUCTS 2913 OILCROPS 2560 COCONUTS (INCL. COPRA) & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2913 OILCROPS 2561 SESAMESEED
2901 TOTAL 2903 VEGETALE PRODUCTS 2913 OILCROPS 2562 PALM KERNELS & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2913 OILCROPS 2563 OLIVES & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2913 OILCROPS 2570 OILCROPS, OTHER & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2571 SOYBEAN OIL
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2572 GROUNDNUT OIL
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2573 SUNFLOWERSEED OIL
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2574 RAPE AND MUSTARD OIL & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2575 COTTONSEED OIL
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2576 PALM KERNEL OIL
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2577 PALM OIL & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2578 COPRA OIL
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2579 SESAMESEED OIL
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2580 OLIVE OIL & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2581 RICE BRAN OIL
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2582 MAIZE GERM OIL
2901 TOTAL 2903 VEGETALE PRODUCTS 2914 VEGETABLE OILS 2586 OILCROPS OIL, OTHER & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2918 VEGETABLES 2601 TOMATOES & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2918 VEGETABLES 2602 ONIONS, DRY
2901 TOTAL 2903 VEGETALE PRODUCTS 2918 VEGETABLES 2605 VEGETABLES, OTHER & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2919 FRUITS (Excluding Wine) 2611 ORANGES AND MANDARINS & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2919 FRUITS (Excluding Wine) 2612 LEMONS AND LIMES & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2919 FRUITS (Excluding Wine) 2613 GRAPEFRUIT & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2919 FRUITS (Excluding Wine) 2614 CITRUS, OTHER & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2919 FRUITS (Excluding Wine) 2615 BANANAS
2901 TOTAL 2903 VEGETALE PRODUCTS 2919 FRUITS (Excluding Wine) 2616 PLANTAINS
2901 TOTAL 2903 VEGETALE PRODUCTS 2919 FRUITS (Excluding Wine) 2617 APPLES (EXCL. CIDER) & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2919 FRUITS (Excluding Wine) 2618 PINEAPPLES & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2919 FRUITS (Excluding Wine) 2619 DATES
2901 TOTAL 2903 VEGETALE PRODUCTS 2919 FRUITS (Excluding Wine) 2620 GRAPES (EXCL. WINE) & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2919 FRUITS (Excluding Wine) 2625 FRUIT, OTHER & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2922 STIMULANTS 2630 COFFEE & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2922 STIMULANTS 2633 COCOA BEANS & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2922 STIMULANTS 2635 TEA & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2923 SPICES 2640 PEPPER
2901 TOTAL 2903 VEGETALE PRODUCTS 2923 SPICES 2641 PIMENTO
2901 TOTAL 2903 VEGETALE PRODUCTS 2923 SPICES 2642 CLOVES
2901 TOTAL 2903 VEGETALE PRODUCTS 2923 SPICES 2645 SPICES, OTHER & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2924 ALCOHOLIC BEVERAGES 2655 WINE & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2924 ALCOHOLIC BEVERAGES 2656 BARLEY BEER
2901 TOTAL 2903 VEGETALE PRODUCTS 2924 ALCOHOLIC BEVERAGES 2657 BEVERAGES, FERMENTED & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2924 ALCOHOLIC BEVERAGES 2658 BEVERAGES, ALCOHOLIC
2901 TOTAL 2903 VEGETALE PRODUCTS 2924 ALCOHOLIC BEVERAGES 2659 ALCOHOL, NON-FOOD
2901 TOTAL 2903 VEGETALE PRODUCTS 2928 MISCELLANEOUS 2680 INFANT FOOD
2901 TOTAL 2941 ANIMAL PRODUCTS 2943 MEAT 2731 BOVINE MEAT & products
2901 TOTAL 2941 ANIMAL PRODUCTS 2943 MEAT 2732 MUTTON/GOAT MEAT & products
2901 TOTAL 2941 ANIMAL PRODUCTS 2943 MEAT 2733 PIG MEAT & products
2901 TOTAL 2941 ANIMAL PRODUCTS 2943 MEAT 2734 POULTRY MEAT & products
2901 TOTAL 2941 ANIMAL PRODUCTS 2943 MEAT 2735 OTHER MEAT & products
2901 TOTAL 2941 ANIMAL PRODUCTS 2945 OFFALS 2736 OFFALS & products
2901 TOTAL 2941 ANIMAL PRODUCTS 2946 ANIMAL FATS 2737 FATS, ANIMAL, RAW & products
2901 TOTAL 2941 ANIMAL PRODUCTS 2946 ANIMAL FATS 2740 BUTTER, GHEE & products
2901 TOTAL 2941 ANIMAL PRODUCTS 2946 ANIMAL FATS 2743 CREAM
2901 TOTAL 2941 ANIMAL PRODUCTS 2949 EGGS 2744 EGGS & products
2901 TOTAL 2903 VEGETALE PRODUCTS 2909 SWEETENERS 2745 HONEY
2901 TOTAL 2941 ANIMAL PRODUCTS 2960 FISH SEAFOOD 2761 Freshwater Fish
2901 TOTAL 2941 ANIMAL PRODUCTS 2960 FISH SEAFOOD 2762 Demersal Fish
2901 TOTAL 2941 ANIMAL PRODUCTS 2960 FISH SEAFOOD 2763 Pelagic Fish
2901 TOTAL 2941 ANIMAL PRODUCTS 2960 FISH SEAFOOD 2764 Marine Fish, Other
2901 TOTAL 2941 ANIMAL PRODUCTS 2960 FISH SEAFOOD 2765 Crustaceans
2901 TOTAL 2941 ANIMAL PRODUCTS 2960 FISH SEAFOOD 2766 Cephalopods
2901 TOTAL 2941 ANIMAL PRODUCTS 2960 FISH SEAFOOD 2767 Molluscs, Other
2901 TOTAL 2941 ANIMAL PRODUCTS 2961 AQUATIC PRODUCTS 2769 Aquatic Animals, Others
2901 TOTAL 2903 VEGETALE PRODUCTS 2961 AQUATIC PRODUCTS 2775 Aquatic Plants
2901 TOTAL 2941 ANIMAL PRODUCTS 2960 FISH SEAFOOD 2781 Fish, Body Oil
2901 TOTAL 2941 ANIMAL PRODUCTS 2960 FISH SEAFOOD 2782 Fish, Liver Oil
2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) 2807 RICE & products
2901 TOTAL 2941 ANIMAL PRODUCTS 2948 MILK (excluding butter) 2848 MILK & products (excl. butter)
2901 TOTAL 2903 VEGETALE PRODUCTS 2928 MISCELLANEOUS 2899 MISCELLANEOUS

Synthetic data base

We decide to add the hiearachy of food items but also the hierarchy of geographical levels and the population in a single file where it will be possible to realize easily all types of analysis.

Joining with `by = join_by(i)`
Joining with `by = join_by(t, s)`
Structure of multilevel database
s t name_s i name_i Kist Pst i0 name_i0 i1 name_i1 i2 name_i2 reg_s cont_s
AFG 2010 Afghanistan 2511 Wheat and products 1469.35 28189.67 2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) Southern Asia Asia
AFG 2011 Afghanistan 2511 Wheat and products 1407.04 29249.16 2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) Southern Asia Asia
AFG 2012 Afghanistan 2511 Wheat and products 1358.96 30466.48 2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) Southern Asia Asia
AFG 2013 Afghanistan 2511 Wheat and products 1390.57 31541.21 2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) Southern Asia Asia
AFG 2014 Afghanistan 2511 Wheat and products 1362.96 32716.21 2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) Southern Asia Asia
AFG 2015 Afghanistan 2511 Wheat and products 1371.68 33753.50 2901 TOTAL 2903 VEGETALE PRODUCTS 2905 CEREALS (excluding beer) Southern Asia Asia 
[1] "data.table" "data.frame"

As we can see the database is a “cube” with three elementary dimensions :

  • \(t\) : the year of observation
  • \(s\) : the state concerned
  • \(i\) : the item of food at the elementary level.

Two quantitative indicators are available :

  • \(F_{ijt}\) : the consumption of food from item \(i\) in state \(s\) during the year \(t\) measured in kcal/capita/day
  • \(P_{it}\) : the estimated population of state \(i\) during the year \(t\) which can be used as weighting criteria for the procedure of agregation if we want to obtain corrected value at world, continental or regional level.

The following columns are different keys of aggregation for food items (i0, i1,i2) or for spatial units (reg, cont)

Data check

The synthetic database has 193650 lines which is less than the total number of value of the cube that should be equal to 96 food items x 189 countries x 13 years = 235872. It means that the matrix is filled at 82% but 18% of values are missing (no data for some countries some years) or equal to 0 (items that are not reported in one country).

Check 1 : total food supply

We can firstly try to compute some global values, for example in 2021.

World level

Food ration in Kcal by day at world level in 2021
Total Kcal Total Population Kcal/capita
2.3377e+10 7850358 2977.826
  • Comment : If our results are correct, the average food ration at world level (weighted by population of countries) is equal to 2978 kcal/capita/day. This value is based on 190 coutries that summarize a total of 7.850 billions of inhabitants. We have verified in the publication of FAO that it is exactly the figure published in the report of the organization FAO (2023).

Continental level

`summarise()` has grouped output by 's'. You can override using the `.groups`
argument.
Food ration in Kcal by day at continental level in 2021
Continent Kcal Population % Kcal % Pop Kcal/capita index 100 = World
Africa 3569201324 1386569 15.27 17.66 2574 86
Asia 13636915976 4650890 58.33 59.24 2932 98
Oceania 136859879 44165 0.59 0.56 3099 104
Americas 3467937046 1026254 14.83 13.07 3379 113
Europe 2566083186 742480 10.98 9.46 3456 116
  • Comment : At continental level, we add some columns in order to compare for each continent the share of population and the share of food consumption measured in Kcal. According to our results, the lowest level of consumption is observed in Africa which account for 17.7% of world population but only 15.3% of food consumption mesured in Kcal. The level of consumption of Africa is at index 86 i.e -14% lower than the world average. On the contrary, Europe is at index 116 which is +16% higher than the world average. As inprevious case, we obtain the same figure than the FAO report FAO (2023).

Regional level

`summarise()` has grouped output by 's'. You can override using the `.groups`
argument.
Food ration in Kcal by day at regional level in 2021
region Kcal Population % Kcal % Pop Kcal/capita index 100 = World
Eastern Africa 1012076340 445491 4.33 5.67 2272 76
Melanesia 27842761 12189 0.12 0.16 2284 77
Middle Africa 458268899 199382 1.96 2.54 2298 77
Southern Asia 5111089355 1989452 21.86 25.34 2569 86
Western Africa 1109898236 418539 4.75 5.33 2652 89
Southern Africa 185122608 67985 0.79 0.87 2723 91
Caribbean 112474137 39486 0.48 0.50 2848 96
South-Eastern Asia 1940288698 669410 8.30 8.53 2899 97
Polynesia 1868998 629 0.01 0.01 2972 100
Micronesia 885953 297 0.00 0.00 2987 100
South America 1348459257 433953 5.77 5.53 3107 104
Central America 558610711 177662 2.39 2.26 3144 106
Western Asia 876043990 278406 3.75 3.55 3147 106
Northern Africa 803835241 255172 3.44 3.25 3150 106
Central Asia 239371457 75898 1.02 0.97 3154 106
Eastern Europe 971750272 290964 4.16 3.71 3340 112
Eastern Asia 5470122478 1637725 23.40 20.86 3340 112
Northern Europe 358664987 105888 1.53 1.35 3387 114
Australia and New Zealand 106262168 31051 0.45 0.40 3422 115
Southern Europe 530564557 150323 2.27 1.91 3530 119
Western Europe 705103371 195306 3.02 2.49 3610 121
Northern America 1448392941 375153 6.20 4.78 3861 130
  • Comment : We can replicate the same analysis at the level of the 22 regions defined by UN as subdivisions of continents. We can reveal more important inequalities with a lowest level of 76 (-24% of world average) in Eastern Africa and an highest level of 130 (+30% of world average) in Northern America. These results are consistent with the publication of the FAO and the litterature on the subject.

Check 2 : food supply by commodity

As a second check, we will try to reproduce the figure below published in the last FAO report FAO (2023).

Fao Figure This example is interesting because FAO does not use the level of aggregation i2 (21 categories) described before but a more aggregated level (9 categories) that we propose to call i2b and store in our database as it is certainly more convenient in most of our future analysis. The aggregation suggested by FAO is decribed below :

Aggregation in 9 food items used by FAO (2023)
i2b name_i2b i2 name_i2
X01 Cereals 2905 CEREALS (excluding beer)
X02 Fats and oils 2913 OILCROPS
X02 Fats and oils 2914 VEGETABLE OILS
X02 Fats and oils 2946 ANIMAL FATS
X03 Sugar 2909 SWEETENERS
X03 Sugar 2908 SUGAR CROPS
X04 Fruits and vegetables 2918 VEGETABLES
X04 Fruits and vegetables 2919 FRUITS (Excluding Wine)
X05 Root, tubers and pulses 2907 STARCHY ROOTS
X05 Root, tubers and pulses 2911 PULSES
X06 Meat 2943 MEAT
X06 Meat 2945 OFFALS
X07 Dairy and eggs (excl. butter) 2949 EGGS
X07 Dairy and eggs (excl. butter) 2948 MILK (excluding butter)
X08 Beverage and other 2912 TREENUTS
X08 Beverage and other 2922 STIMULANTS
X08 Beverage and other 2923 SPICES
X08 Beverage and other 2924 ALCOHOLIC BEVERAGES
X08 Beverage and other 2928 MISCELLANEOUS
X09 Fish and seefod 2960 FISH SEAFOOD
X09 Fish and seefod 2961 AQUATIC PRODUCTS

So, we add this new key to our database and store it for further use.

Joining with `by = join_by(i2, name_i2)`

We can know try to build the figure of FAO report by agregating the data according to continents and item code i2b.

`summarise()` has grouped output by 's', 'cont_s', 'i2b'. You can override
using the `.groups` argument.
`summarise()` has grouped output by 'cont_s', 'i2b'. You can override using the
`.groups` argument.
Adding missing grouping variables: `cont_s`
Food ration in Kcal by continent and type of products (%)
name Africa Americas Asia Europe Oceania
X01 Cereals 45.5 28.2 48.6 28.2 22.2
X02 Fats and oils 12.6 20.0 13.8 19.8 20.9
X03 Sugar 6.1 13.5 6.5 10.7 11.3
X04 Fruits and vegetables 6.5 6.0 8.6 6.0 7.3
X05 Root, tubers and pulses 20.0 5.3 5.4 4.4 7.8
X06 Meat 3.5 11.9 6.3 10.2 12.6
X07 Dairy and eggs (excl. butter) 2.9 9.7 6.1 12.4 8.7
X08 Beverage and other 2.2 4.8 3.0 7.1 8.0
X09 Fish and seefod 0.7 0.7 1.7 1.2 1.2

The table seems clearly in line with the figure of FAO report that we can try to replicate approximatively :

Check 3 : Map of evolution

Finally, we try to replicate a map of evolution of total food supply by state between 2018-2019 and 2020-2021, created by FAO in order to evaluate the effects of Covid crisis :

Fao Figure

We can firsly produce the dataset :

`summarise()` has grouped output by 's'. You can override using the `.groups`
argument.
Variation of total food supply in Kcal/capita/day
state Average 2018-19 Average 2020-21 Absolute variation
AFG 2247 2253 6
AGO 2439 2400 -40
ALB 3364 3340 -24
ARE 3267 3307 41
ARG 3254 3327 73
ARM 3203 3222 19

Then we link the data with the map file by ISO3 code and realize the map using the same statistical breaks than FAO report :

The resulting map is the same than the one published by FAO. They are only differences concerning the country with missing values. Northern Korea and South Sudan are not avilable on our map but present in FAO report. The reverse is true for Somalia.

Conclusion

We have successfully created table, graphics and maps that are exactly the same than the one published by FAO (except very minor differences) and we can therefore conclude that the creation of the database and the linkage with map files is correct.

We will further enlarged the database to other indicators but the experiment on energy supply in Kcal is sufficient for the moment.

A more difficult challenge will be to produce long terme tome series from 1963 to present but this point will be discussed in another chapter of the website.

References

FAO. 2023. “Food Balance Sheets 2020-2021 - Global, Regional and Country Trends.” Rome. https://doi.org/10.4060/cc8088en.